<?php
require_once 'tools/constant/Config.php';
class DbUtils{
	private $serveur=Config::serveur;
	private $user=Config::user;
	private $password=Config::password;
	private $database=Config::database;
	private $connect=null;


	function getLastId(){
		return mysql_insert_id($this->connect);
	}
	function connect(){
		
		if ($this->connect =mysql_connect($this->serveur,$this->user,$this->password)){
			mysql_select_db($this->database,$this->connect);
			return true;
		}else{
			$message="Could not connect to database: ".$this->database. "\n";
			$message.="Server: ".$this->serveur. "\n";
			$message.="User: ".$this->user. "\n";
			$message.="Password: ".$this->password. "\n";
			die ($message);
		}
		return false;
	}

	function isConnected(){
		return $this->connect!=null;

	}
	function close(){
		if (isset($this->connect)){
			if (mysql_close($this->connect) ){
				$this->connect=null;
				return true;
			}else{
				die("Cannot close Db connection");
			}
		}else{
			die("la connexion bd ne peut etre car elle n\'a pas été préalablement ouverte");

		}

	}
	function isDossardInCurrentCourse($dossardNumber){
		$results=0;
		$queryresult=mysql_query("select * from equipe_subscribed where course_key like'".$this->getCourse()."%' AND dossard=".$dossardNumber );
		if ($queryresult) {
			$results=mysql_num_rows($queryresult);
		}
		return $results>0;
	}
	function isDossardValidForReSubscribe($dossardNumber){
		$results=0;
		$sql="select * from equipe_subscribed where course_key !='".$this->getCourse()."' AND dossard=".$dossardNumber;
		
		$queryresult=mysql_query($sql );
		if ($queryresult) {
			$results=mysql_num_rows($queryresult);
		}
		return $results>0;
	}
	function isDossardValid($dossardNumber){
		$results=0;
		$sql="select * from equipe where dossard=".$dossardNumber;
		$queryresult=mysql_query($sql);
		if ($queryresult) {
			$results=mysql_num_rows($queryresult);
		}else{
			die("REQUETE SQL INVALIDE".$sql);
		}
		return $results==0;
	}
	
	
	function manageNewRunnerData($nom,$prenom,$date,$sexe,$club){
		$query = sprintf("select * from runnerdata where nom=upper('%s') AND prenom=upper('%s')",
		mysql_real_escape_string($nom),
		mysql_real_escape_string($prenom));

		$queryresult=mysql_query($query);
		if (!$queryresult) {
			$message  = 'Requete invalide : ' . mysql_error() . "\n";
			$message .= 'Requete complete : ' . $query;
			die($message);
		}
		if(mysql_num_rows($queryresult)==0){
			$queryresult=mysql_query("insert into runnerdata (nom,prenom,date,sexe,club)values(upper('".$nom."'),upper('".$prenom."'),'".$date."','".$sexe."','".$club."')");
		}
		if (!$queryresult) {
			$message  = 'Requete invalide : ' . mysql_error() . "\n";
			$message .= 'Requete complete : ' . $query;
			die($message);
		}

		return $queryresult;


	}

	function saveRunner($nom,$prenom,$date,$sexe,$club){
		$this->manageNewRunnerData($nom,$prenom,$date,$sexe,$club);
		$query="insert into runner (nom,prenom,date,sexe,club)values(upper('".$nom."'),upper('".$prenom."'),'".$date."','".$sexe."','".$club."')";
		$queryresult=mysql_query($query);
		if (!$queryresult) {
			$message  = 'Requete invalide : ' . mysql_error() . "\n";
			$message .= 'Requete complete : ' . $query;
			die($message);
		}
	}
	function saveTeam($idRunner1,$idRunner2,$dossard){
		$query="insert into equipe (fk_first_runner,fk_second_runner,dossard)values(".$idRunner1.",".$idRunner2.",".$dossard.")";
		$queryresult=mysql_query($query);
		if (!$queryresult) {
			$message  = 'Requete invalide : ' . mysql_error() . "\n";
			$message .= 'Requete complete : ' . $query;
			die($message);
		}
	}
	function updateRunner($nom,$prenom,$runnerId,$sexe){
		$query="update runner set nom=upper('".$nom."'), prenom=upper('".$prenom."') , sexe='".$sexe."' where id=".$runnerId;
		$queryresult=mysql_query($query);
		if (!$queryresult) {
			$message  = 'Requete invalide : ' . mysql_error() . "\n";
			$message .= 'Requete complete : ' . $query;
			die($message);
		}
	}
	
	function updateDossard($oldDossard,$newDossard,$teamId){
		$query="update equipe set dossard='".trim($newDossard)."' where dossard='".trim($oldDossard)."' and id=".$teamId;
		$queryresult=mysql_query($query);
		if (!$queryresult) {
			$message  = 'Requete invalide : ' . mysql_error() . "\n";
			$message .= 'Requete complete : ' . $query;
			die($message);
		}
	}
	function updateResultTime($resultId,$course,$hour="00",$min="00",$sec="00"){
		$time=$hour.":".$min.":".$sec;
		$query="update resultats set temp=(select ADDTIME(time_reference,'".$time."') from timer_setting where course='".$course."') where course_key='".$course."' and id=".$resultId;
		$queryresult=mysql_query($query);
		if (!$queryresult) {
			$message  = 'Requete invalide : ' . mysql_error() . "\n";
			$message .= 'Requete complete : ' . $query;
			die($message);
		}
		
	}
	function getAllRunnersData(){

		$queryresult=mysql_query("select * from runnerdata order by nom,prenom");


		$i=0;
		while ($row= mysql_fetch_assoc($queryresult)){

			$tab[$i]=$row;
			$i++;

		}

		if(isset($tab)){
			return $tab;
		}
		return 0;

	}
	function getAllRaceTeams(){

		$query="select
					team.id,
					team.dossard,
					run1.id as runner1Id,
					run2.id as runner2Id,
					run1.nom as nom1,
					run1.prenom as prenom1,
					run2.nom as nom2,
					run2.prenom as prenom2,
					run1.sexe as sexe1,	
					run2.sexe as sexe2
				from equipe team 
					join runner run1 on run1.id=team.fk_first_runner
					join runner run2 on run2.id=team.fk_second_runner 
				order by team.dossard";
		$queryresult=mysql_query($query);
		$i=0;
		while ($row= mysql_fetch_assoc($queryresult)){

			$tab[$i]=$row;
			$i++;

		}

		if(isset($tab)){
			return $tab;
		}
		return 0;

	}
	
	function getAllTeams(){
	
		$query="select
					team.dossard,
					run1.nom as nom1,
					run1.prenom as prenom1,
					run2.nom as nom2,
					run2.prenom as prenom2
				from equipe team
					join runner run1 on run1.id=team.fk_first_runner
					join runner run2 on run2.id=team.fk_second_runner
					join equipe_subscribed sub on team.id=sub.fk_team
					where sub.course_key like '".$this->getCourse()."%'";
		$queryresult=mysql_query($query);
		$i=0;
		while ($row= mysql_fetch_assoc($queryresult)){
	
			$tab[$i]=$row;
			$i++;
	
		}
	
		if(isset($tab)){
			return $tab;
		}
		return 0;
	
	
	
	}
	
	function executeQueryLimit($query,$nbRows){
		
		if(isset($query)){
			$queryresult=mysql_query($query);
			if (!$queryresult) {
				$message  = 'Requete invalide : ' . mysql_error() . "\n";
				$message .= 'Requete complete : ' . $query;
				die($message);
			}
			$i=0;
			while ($row= mysql_fetch_assoc($queryresult)){
				$tab[$i]=$row;
				$i++;
			}

			
			if(isset($tab)){
				return $tab;
			}
		}else{
			die ("QUERY: (".$query.") Is not found");
		}
		return 0;

	}
	function executeQuery($query){
		
		if(isset($query)){
			$queryresult=mysql_query($query);
			if (!$queryresult) {
				$message  = 'Requete invalide : ' . mysql_error() . "\n";
				$message .= 'Requete complete : ' . $query;
				die($message);
			}
			$i=0;
			while ($row= mysql_fetch_assoc($queryresult)){
				$tab[$i]=$row;
				$i++;
			}

			
			if(isset($tab)){
				return $tab;
			}
		}else{
			die ("QUERY: (".$query.") Is not found");
		}
		return 0;

	}
	function getleftRunners(){
		$course=$this->getCourse();
		$tableLeft=$this->executeQuery("select
					team.dossard,
					run1.nom as nom1,
					run1.prenom as prenom1,
					run2.nom as nom2,
					run2.prenom as prenom2
				from equipe team 
					join runner run1 on run1.id=team.fk_first_runner
					join runner run2 on run2.id=team.fk_second_runner
					join equipe_subscribed sub on team.id=sub.fk_team
				where 
					sub.course_key='".$course."' and team.id not in (select FK_team from resultats where course_key like '".$course."%')");


		return $tableLeft;
	}
	
	function subscribeOld($dossard,$distance=""){
		$saved=false;
		if(!$this->isDossardValid($dossard)){
			$saved=true;
			$query="select team.id	from equipe team where team.dossard=".$dossard;
			$queryresult=mysql_query($query);
			if (!$queryresult) {
				$message  = 'Requete invalide : ' . mysql_error() . "\n";
				$message .= 'Requete complete : ' . $query;
				die($message);
			}
			$row=mysql_fetch_assoc($queryresult);
			$id=$row['id'];
			$courseKey=$this->getCourse();
			if(!Config::team){
				$courseKey.="_".$distance;
			}
			
			$queryresult=mysql_query("insert into equipe_subscribed (fk_team,dossard,course_key) values (".$id.",".$dossard.",'".$courseKey."')");
			if (!$queryresult) {
				$message  = 'Requete invalide : ' . mysql_error() . "\n";
				$message .= 'Requete complete : ' . $query;
				die($message);
			}
			
		}
		return $saved;
	}
	function setRunner($dossard){
		$saved=false;
		if(!$this->isDossardValid($dossard)){
			$saved=true;
			$query="select
						team.id,
						team.fk_first_runner,
						team.fk_second_runner,
						sub.course_key
					from equipe team 
					join equipe_subscribed sub on team.id=sub.fk_team
					where team.dossard=".$dossard."  and sub.course_key like '".$this->getCourse()."%'";
			$queryresult=mysql_query($query);
			if (!$queryresult) {
				$message  = 'Requete invalide : ' . mysql_error() . "\n";
				$message .= 'Requete complete : ' . $query;
				die($message);
			}
			$row=mysql_fetch_assoc($queryresult);
			$id=$row['id'];
	
			$first=$row['fk_first_runner'];
			$second=$row['fk_second_runner'];
			$courseName=$row['course_key'];
			
			$queryresult=mysql_query("insert into resultats (FK_team,fk_runner1,fk_runner2,course_key) values (".$id.",".$first.",".$second.",'".$courseName."')");
			if (!$queryresult) {
				$message  = 'Requete invalide : ' . mysql_error() . "\n";
				$message .= 'Requete complete : ' . $query;
				die($message);
			}
			
		}
		return $saved;
		

	}
	function resetDB(){

		
		$queryresult=mysql_query("delete from resultats where course_key='".$this->getCourse()."'");
		$queryresult=mysql_query("delete from equipe_subscribed where course_key='".$this->getCourse()."'");
		
		return $queryresult;

	}
	function resetDBALL(){

		
		$queryresult=mysql_query("delete from resultats");
		$queryresult=mysql_query("delete from equipe");
		$queryresult=mysql_query("delete from runner");
		$queryresult=mysql_query("delete from equipe_subscribed");
		
		return $queryresult;

	}
	function setCourse($course){
		$queryresult=mysql_query("delete from system");
		$queryresult=mysql_query("insert into system (course_key) values ('".$course."')");
		return $queryresult;

	}
	function  getCourse(){
		$queryresult=mysql_query("select * from system");
		$row=mysql_fetch_assoc($queryresult);
		$time=$row['course_key'];
		return $time;

	}
	function setTime(){
		$queryresult=mysql_query("delete from timer_setting where time='time' and course='".$this->getCourse()."'");
	
		$queryresult=mysql_query("insert into timer_setting (time,course) values ('time','".$this->getCourse()."')");
		return $queryresult;

	}
	function getTime($course){
		$queryresult=mysql_query("select * from timer_setting where course='".$course."'");
		
		$row=mysql_fetch_assoc($queryresult);
		$time=$row['time_reference'];
		
		return $time;

	}


}
?>